-- This script demonstrates the danger of using NOT IN.
-- © Erland Sommarskog 2020.

USE SQLTitbits;
go
-- This are all our customer responsibles. Make a note of the number.
SELECT * FROM dbo.CustomerResponsibles;

-- All customer responsibles who are located in the same city as a customer 
-- (not necessarily one of their own customers).
SELECT * 
FROM   dbo.CustomerResponsibles CR
WHERE  CR.Location IN (SELECT C.City FROM dbo.Customers C);

-- All customer responsibles who are NOT located in a city where there 
-- is a customer.
SELECT * 
FROM   dbo.CustomerResponsibles CR
WHERE  CR.Location NOT IN (SELECT C.City FROM dbo.Customers C);

-- All customer responsibles who actually have customers assigned.
SELECT * 
FROM   dbo.CustomerResponsibles CR
WHERE  CR.CustRespID IN (SELECT C.CustRespID FROM dbo.Customers C);

-- Intended to return all responsibles who have not been assigned
-- a customer - but comes back empty-handed, which is wrong.
SELECT * 
FROM   dbo.CustomerResponsibles CR
WHERE  CR.CustRespID NOT IN (SELECT C.CustRespID FROM dbo.Customers C);

-- The subquery returns the distinct values 1, 2, 3, 4, 6, 7, 9 and NULL,
-- so this is the equivalent. For the row where CustRespID = 5 what does the
-- condition evaluate to? And why?
SELECT * FROM dbo.CustomerResponsibles
WHERE  NOT (CustRespID IN (1, 2, 3, 4, 6, 7, 9, NULL));
-- Same as:
-- NOT (CustRespID = 1 OR CustRespID = 2 OR ... OR CustRespID = NULL)

-- If we add a condition filter out the NULL values, we have better success.
SELECT * 
FROM   dbo.CustomerResponsibles CR
WHERE  CR.CustRespID NOT IN 
   (SELECT C.CustRespID FROM dbo.Customers C WHERE C.CustRespID IS NOT NULL);    

-- While that works, this is better in my opinion, because the NOT EXISTS
-- operator is more versatile. This is a *correlated* subquery, which 
-- logically is evaluated for ever row in the outer query.
SELECT *
FROM   dbo.CustomerResponsibles CR
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.Customers C
                   WHERE  C.CustRespID = CR.CustRespID);


-- And we can use EXISTS instead of IN. While it may sound inefficient that
-- it is evaluated for every row, the plan is exactly the same as for the
-- query with IN.
SELECT *
FROM   dbo.CustomerResponsibles CR
WHERE  EXISTS (SELECT *
               FROM   dbo.Customers C
               WHERE  C.CustRespID = CR.CustRespID);


-- Here is the true power of (NOT) EXISTS. IN is limited to single-column
-- conditions. With (NOT) EXISTS you can have as many conditions as you need.
-- List all responsibles who are located in the same city as a customer they 
-- are responsible for.
SELECT *
FROM   dbo.CustomerResponsibles CR
WHERE  EXISTS (SELECT *
               FROM   dbo.Customers C
               WHERE  C.CustRespID = CR.CustRespID
                 AND  C.City       = CR.Location);
go

-- Also watch out for this. The subquery for EXISTS should 
-- normally reference the outer query (be correlated as term
-- is). The below means "Display all customer responsibles if 
-- and only if there is at least one customer in Stockholm." 
-- Does it sound odd? Well, it is.
SELECT *
FROM   dbo.CustomerResponsibles CR
WHERE  EXISTS (SELECT * 
               FROM  dbo.Customers C
               WHERE C.City = 'Stockholm');
